const XLSX = require('xlsx');
const { pool } = require('../database');

/**
 * Excel服务类
 * 负责生成Excel文件相关功能
 */
class ExcelService {
  constructor() {
    this.workbook = null;
  }

  /**
   * 生成预约数据Excel文件
   * @param {Object} options - 选项
   * @param {string} options.hospitalId - 医院ID
   * @param {string} options.startDate - 开始日期
   * @param {string} options.endDate - 结束日期
   * @param {string} options.hospitalName - 医院名称
   * @returns {Object} 包含Excel缓冲区和统计信息
   */
  async generateAppointmentReport(options) {
    try {
      const { hospitalId, startDate, endDate, hospitalName } = options;
      
      // 获取预约数据
      const appointmentData = await this.getAppointmentData(hospitalId, startDate, endDate);
      
      // 获取统计信息
      const statistics = await this.getAppointmentStatistics(hospitalId, startDate, endDate);
      
      // 创建Excel工作簿
      this.workbook = XLSX.utils.book_new();
      
      // 生成预约详情表
      this.generateAppointmentSheet(appointmentData);
      
      // 生成统计汇总表
      this.generateStatisticsSheet(statistics, hospitalName, startDate, endDate);
      
      // 生成医生预约统计表
      await this.generateDoctorStatsSheet(hospitalId, startDate, endDate);
      
      // 生成科室预约统计表
      await this.generateDepartmentStatsSheet(hospitalId, startDate, endDate);
      
      // 生成Excel缓冲区
      const excelBuffer = XLSX.write(this.workbook, { 
        type: 'buffer', 
        bookType: 'xlsx',
        compression: true
      });
      
      const fileName = `预约数据报告_${hospitalName}_${startDate}_${endDate}.xlsx`;
      
      return {
        buffer: excelBuffer,
        fileName,
        statistics: {
          date: `${startDate} 至 ${endDate}`,
          hospitalName,
          totalAppointments: statistics.totalAppointments,
          confirmedAppointments: statistics.confirmedAppointments,
          pendingAppointments: statistics.pendingAppointments,
          cancelledAppointments: statistics.cancelledAppointments,
          completedAppointments: statistics.completedAppointments
        }
      };
    } catch (error) {
      console.error('生成Excel文件失败:', error);
      throw error;
    }
  }

  /**
   * 获取预约数据
   */
  async getAppointmentData(hospitalId, startDate, endDate) {
    const [rows] = await pool.execute(`
      SELECT 
        a.id as 预约ID,
        a.appointment_date as 预约日期,
        CASE 
          WHEN a.time_slot_type = 1 THEN '上午'
          WHEN a.time_slot_type = 2 THEN '下午'
          ELSE '未知'
        END as 时段,
        a.patient_name as 患者姓名,
        a.patient_phone as 患者电话,
        a.symptoms as 症状描述,
        CASE 
          WHEN a.status = 'pending' THEN '待确认'
          WHEN a.status = 'confirmed' THEN '已确认'
          WHEN a.status = 'cancelled' THEN '已取消'
          WHEN a.status = 'completed' THEN '已完成'
          ELSE '未知'
        END as 预约状态,
        a.order_time as 下单时间,
        d.name as 医生姓名,
        dep.name as 科室名称,
        d.title as 医生职称,
        d.registration_fee as 挂号费
      FROM appointments a
      LEFT JOIN doctors d ON a.doctor_id = d.id
      LEFT JOIN departments dep ON d.department_id = dep.id
      LEFT JOIN hospitals h ON d.hospital_id = h.id
      WHERE h.id = ? 
        AND a.appointment_date BETWEEN ? AND ?
      ORDER BY a.appointment_date DESC, a.order_time DESC
    `, [hospitalId, startDate, endDate]);

    return rows;
  }

  /**
   * 获取预约统计信息
   */
  async getAppointmentStatistics(hospitalId, startDate, endDate) {
    const [rows] = await pool.execute(`
      SELECT 
        COUNT(*) as totalAppointments,
        SUM(CASE WHEN a.status = 'pending' THEN 1 ELSE 0 END) as pendingAppointments,
        SUM(CASE WHEN a.status = 'confirmed' THEN 1 ELSE 0 END) as confirmedAppointments,
        SUM(CASE WHEN a.status = 'cancelled' THEN 1 ELSE 0 END) as cancelledAppointments,
        SUM(CASE WHEN a.status = 'completed' THEN 1 ELSE 0 END) as completedAppointments,
        SUM(d.registration_fee) as totalRevenue
      FROM appointments a
      LEFT JOIN doctors d ON a.doctor_id = d.id
      LEFT JOIN hospitals h ON d.hospital_id = h.id
      WHERE h.id = ? 
        AND a.appointment_date BETWEEN ? AND ?
    `, [hospitalId, startDate, endDate]);

    return rows[0];
  }

  /**
   * 生成预约详情表
   */
  generateAppointmentSheet(data) {
    const worksheet = XLSX.utils.json_to_sheet(data);
    
    // 设置列宽
    const colWidths = [
      { wch: 10 }, // 预约ID
      { wch: 12 }, // 预约日期
      { wch: 8 },  // 时段
      { wch: 12 }, // 患者姓名
      { wch: 15 }, // 患者电话
      { wch: 20 }, // 症状描述
      { wch: 10 }, // 预约状态
      { wch: 20 }, // 下单时间
      { wch: 12 }, // 医生姓名
      { wch: 15 }, // 科室名称
      { wch: 12 }, // 医生职称
      { wch: 10 }  // 挂号费
    ];
    worksheet['!cols'] = colWidths;
    
    XLSX.utils.book_append_sheet(this.workbook, worksheet, '预约详情');
  }

  /**
   * 生成统计汇总表
   */
  generateStatisticsSheet(statistics, hospitalName, startDate, endDate) {
    const data = [
      ['医院预约数据统计报告'],
      [''],
      ['报告期间', `${startDate} 至 ${endDate}`],
      ['医院名称', hospitalName],
      ['生成时间', new Date().toLocaleString('zh-CN')],
      [''],
      ['统计项目', '数量', '占比'],
      ['总预约数', statistics.totalAppointments, '100%'],
      ['待确认预约', statistics.pendingAppointments, this.calculatePercentage(statistics.pendingAppointments, statistics.totalAppointments)],
      ['已确认预约', statistics.confirmedAppointments, this.calculatePercentage(statistics.confirmedAppointments, statistics.totalAppointments)],
      ['已取消预约', statistics.cancelledAppointments, this.calculatePercentage(statistics.cancelledAppointments, statistics.totalAppointments)],
      ['已完成预约', statistics.completedAppointments, this.calculatePercentage(statistics.completedAppointments, statistics.totalAppointments)],
      [''],
      ['总收入', `¥${statistics.totalRevenue || 0}`, '']
    ];

    const worksheet = XLSX.utils.aoa_to_sheet(data);
    
    // 设置列宽
    worksheet['!cols'] = [{ wch: 20 }, { wch: 15 }, { wch: 15 }];
    
    XLSX.utils.book_append_sheet(this.workbook, worksheet, '统计汇总');
  }

  /**
   * 生成医生预约统计表
   */
  async generateDoctorStatsSheet(hospitalId, startDate, endDate) {
    const [rows] = await pool.execute(`
      SELECT 
        d.name as 医生姓名,
        dep.name as 科室名称,
        d.title as 医生职称,
        COUNT(a.id) as 预约总数,
        SUM(CASE WHEN a.status = 'confirmed' THEN 1 ELSE 0 END) as 已确认预约,
        SUM(CASE WHEN a.status = 'pending' THEN 1 ELSE 0 END) as 待确认预约,
        SUM(CASE WHEN a.status = 'cancelled' THEN 1 ELSE 0 END) as 已取消预约,
        SUM(CASE WHEN a.status = 'completed' THEN 1 ELSE 0 END) as 已完成预约,
        SUM(d.registration_fee) as 总收入
      FROM doctors d
      LEFT JOIN departments dep ON d.department_id = dep.id
      LEFT JOIN appointments a ON d.id = a.doctor_id AND a.appointment_date BETWEEN ? AND ?
      LEFT JOIN hospitals h ON d.hospital_id = h.id
      WHERE h.id = ?
      GROUP BY d.id, d.name, dep.name, d.title
      ORDER BY 预约总数 DESC
    `, [startDate, endDate, hospitalId]);

    const worksheet = XLSX.utils.json_to_sheet(rows);
    
    // 设置列宽
    worksheet['!cols'] = [
      { wch: 12 }, // 医生姓名
      { wch: 15 }, // 科室名称
      { wch: 12 }, // 医生职称
      { wch: 10 }, // 预约总数
      { wch: 12 }, // 已确认预约
      { wch: 12 }, // 待确认预约
      { wch: 12 }, // 已取消预约
      { wch: 12 }, // 已完成预约
      { wch: 12 }  // 总收入
    ];
    
    XLSX.utils.book_append_sheet(this.workbook, worksheet, '医生统计');
  }

  /**
   * 生成科室预约统计表
   */
  async generateDepartmentStatsSheet(hospitalId, startDate, endDate) {
    const [rows] = await pool.execute(`
      SELECT 
        dep.name as 科室名称,
        COUNT(a.id) as 预约总数,
        SUM(CASE WHEN a.status = 'confirmed' THEN 1 ELSE 0 END) as 已确认预约,
        SUM(CASE WHEN a.status = 'pending' THEN 1 ELSE 0 END) as 待确认预约,
        SUM(CASE WHEN a.status = 'cancelled' THEN 1 ELSE 0 END) as 已取消预约,
        SUM(CASE WHEN a.status = 'completed' THEN 1 ELSE 0 END) as 已完成预约,
        SUM(d.registration_fee) as 总收入
      FROM departments dep
      LEFT JOIN doctors d ON dep.id = d.department_id
      LEFT JOIN appointments a ON d.id = a.doctor_id AND a.appointment_date BETWEEN ? AND ?
      LEFT JOIN hospitals h ON dep.hospital_id = h.id
      WHERE h.id = ?
      GROUP BY dep.id, dep.name
      ORDER BY 预约总数 DESC
    `, [startDate, endDate, hospitalId]);

    const worksheet = XLSX.utils.json_to_sheet(rows);
    
    // 设置列宽
    worksheet['!cols'] = [
      { wch: 15 }, // 科室名称
      { wch: 10 }, // 预约总数
      { wch: 12 }, // 已确认预约
      { wch: 12 }, // 待确认预约
      { wch: 12 }, // 已取消预约
      { wch: 12 }, // 已完成预约
      { wch: 12 }  // 总收入
    ];
    
    XLSX.utils.book_append_sheet(this.workbook, worksheet, '科室统计');
  }

  /**
   * 计算百分比
   */
  calculatePercentage(value, total) {
    if (total === 0) return '0%';
    return `${((value / total) * 100).toFixed(1)}%`;
  }
}

module.exports = new ExcelService();
